﻿using Logistics.EntityFrameworkCore;
using Logistics.Model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;
using MySqlConnector;
using System.Data;
using Logistics.Pageing;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Logistics.PersonnelMatters
{
    public class PersonnelMattersRepository : DapperRepository<LogisticsDbContext>,IPersonnelMattersRepository
    {
        private readonly LogisticsDbContext _db;
        readonly string SqlConn = "Server=10.31.64.9;Port=3306;Database=Logistics;Uid=si;Pwd=111111;";

        public PersonnelMattersRepository(LogisticsDbContext db, IDbContextProvider<LogisticsDbContext> dbContextProvider):base(dbContextProvider)
        {
            _db = db;
        }

        #region 人事看板
        /// <summary>
        /// 总人数
        /// </summary>
        /// <returns></returns>
        public async Task<int> Headcount()
        {
            var sumCount = await _db.Basic.CountAsync();
            return sumCount;
        }

        /// <summary>
        /// 入职人数
        /// </summary>
        /// <returns></returns>
        public async Task<int> InductionCount()
        {
            var count = await _db.Basic.CountAsync(a => a.Bzt < Convert.ToInt32(EmployeeStatus.Resigned) && a.Bzt >= Convert.ToInt32(EmployeeStatus.ToBeEmployed));
            return count;
        }

        /// <summary>
        /// 离职人数
        /// </summary>
        /// <returns></returns>
        public async Task<int> DimissionCount()
        {
            var count = await _db.Basic.CountAsync(a => a.Bzt == Convert.ToInt32(EmployeeStatus.Resigned));
            return count;
        }

        /// <summary>
        /// 获取员工年龄比
        /// </summary>
        /// <returns></returns>
        public async Task<List<AgeRatio>> GetEmployeeAgeRatio()
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str =
                    "select Age,count(Age) Count from (select `Name`,(SELECT  TIMESTAMPDIFF(YEAR, Bdate, CURDATE())) Age from basic ) T GROUP BY Age ";
                var ageratio =(List<AgeRatio>)await conn.QueryAsync<AgeRatio>(str);
                return ageratio;
            }
        }
        /// <summary>
        /// 获取员工年龄比
        /// </summary>
        /// <returns></returns>
        public async Task<List<AgeRatio>> GetSeniorityRatio()
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str = "select Age,Count(Age) Count from (select(SELECT  TIMESTAMPDIFF(YEAR, Bentry, CURDATE())) Age from basic )T GROUP BY age";
                var ageratio =(List<AgeRatio>)await conn.QueryAsync<AgeRatio>(str);
                return ageratio;
            }
        }

        /// <summary>
        /// 获取部门人数
        /// </summary>
        /// <returns></returns>
        public async Task<List<NumberOfDepartments>> GetDepartment()
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str = "select `Name`,Count from deptinfo d join (select Did,Count(Did) Count from basic GROUP BY Did)T on d.Id=T.Did";
                var department = (List<NumberOfDepartments>)await conn.QueryAsync<NumberOfDepartments>(str);
                return department;
            }
        }

        /// <summary>
        /// 获取离职人数
        /// </summary>
        /// <returns></returns>
        public  async Task<List<AgeRatio>> GetNumberOfSeparations()
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str = "select age,count(age) count from ( select  month(d.dfinally) age  from basic b  join  dimission d on b.id=d.xid  )T GROUP BY age";
                var age = (List<AgeRatio>)await conn.QueryAsync<AgeRatio>(str);
                return age;
            }
        }

        #endregion


        /// <summary>
        /// 获取部门集合
        /// </summary>
        /// <returns></returns>
        public async Task<List<Deptinfo>> GetDeptinfoList()
        {
            var dep = await _db.Deptinfo.ToListAsync();
            return dep;
        }

        /// <summary>
        /// 获取职位集合
        /// </summary>
        /// <returns></returns>
        public async Task<List<PositionInfo>> GetPositionInfoList(int id)
        {
            var posi = await _db.PositionInfo.Where(a => a.Deptid == id).ToListAsync();
            return posi;
        }

        #region 人事登记
        /// <summary>
        /// 新增员工
        /// </summary>
        /// <param name="basic"></param>
        /// <returns></returns>
        public async Task<int> AddBasic(Basic basic)
        {
            basic.Bdatess = DateTime.Now;
            basic.Bentry = DateTime.Now;
            await _db.Basic.AddAsync(basic);
            int i = await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 删除员工数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DelBasicById(string id)
        {
            var ids = id.Split(',');
            foreach (var item in ids)
            {
                var obj = await _db.Basic.FindAsync(Convert.ToInt32(item));
                _db.Basic.Remove(obj);
            }
            int i = await _db.SaveChangesAsync();
            return i;
        }


        /// <summary>
        /// 根据编号获取员工数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Basic> GetBasicById(int id)
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                var str = $@"select a.*,d.`Name` DepName,p.`Name` TName from Basic a join deptinfo d  on a.Did=d.Id join PositionInfo p on a.Tid=p.Id  where  a.Id = {id} ";
                var res = (Basic)await conn.QueryFirstAsync<Basic>(str);
                return res;
            }
        }

        /// <summary>
        /// 获取员工数据
        /// </summary>
        /// <param name="name">通过员工名称进行模糊查询</param>
        /// <param name="phone">通过员工手机号进行模糊查询</param>
        /// <param name="departmentId">通过部门进行查询</param>
        /// <param name="category">通过类别进行查询</param>
        /// <param name="positionId">通过职位进行查询</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页容量</param>
        /// <returns></returns>
        public async Task<BasucPageIng> GetBasicList(string name, string phone, int departmentId, string category, int positionId, int pageIndex, int pageSize)
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                var str = "select a.Id, a.`Name`,a.Sex,d.`Name` DepName,p.`Name` TName, a.Bphone,a.Bacademy,a.Bmajor,a.Bcolumn,a.Bentry,a.Category,a.Bzt,a.Bdatess from Basic a  join  deptinfo d  on a.Did=d.Id join  PositionInfo p on a.Tid=p.Id where  1 = 1 ";
                var strCount = "select count(1) from Basic  where  1 = 1";
                if (!string.IsNullOrEmpty(name))
                {
                    str += $" and a.`Name` like '%{name.Trim()}%'  ";
                    strCount += $" and `Name` like '%{name.Trim()}%'  ";
                }

                if (!string.IsNullOrEmpty(phone))
                {
                    str += $"  and a.Bphone like '%{phone.Trim()}%'";
                    strCount += $"  and Bphone like '%{phone.Trim()}%'";
                }

                if (departmentId != 0)
                {
                    str += $" and  a.Did  = {departmentId}";
                    strCount += $" and  Did  = {departmentId}";
                }

                if (!string.IsNullOrEmpty(category))
                {
                    str += $" and a.Category ='{category.Trim()}'";
                    strCount += $" and Category ='{category.Trim()}'";
                }

                if (positionId != 0)
                {
                    str += $" and a.Tid ={positionId}";
                    strCount += $" and Tid ={positionId}";
                }

                var page = (pageIndex - 1) * pageSize;
                str += $" limit {page},{pageSize}";
                var res = (List<Basic>)await conn.QueryAsync<Basic>(str);
                var total = (int)await conn.QueryFirstAsync<int>(strCount);
                var data = new BasucPageIng
                {
                    Item = res,
                    Toacl = total,
                };
                return data;
            }
        }



        /// <summary>
        /// 修改员工数据
        /// </summary>
        /// <param name="basic"></param>
        /// <returns></returns>
        public async Task<int> PutBasic(Basic basic)
        {
            _db.Entry(basic).State = EntityState.Modified;
            int i = await _db.SaveChangesAsync();
            return i;
        }


        #endregion


        #region 入职

        /// <summary>
        /// 新增入职记录
        /// </summary>
        /// <param name="entry"></param>
        /// <returns></returns>
        public async Task<int> AddEntryData(Entry entry)
        {   
            entry.Edatess= DateTime.Now;
            entry.Ezt = 0;//待提交
            await _db.Entry.AddAsync(entry);
            int i= await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 通过部门编号获取该部门的员工数据
        /// </summary>
        /// <param name="depId"></param>
        /// <returns></returns>
         public async Task<List<Basic>> GetBasicByDepId(int depId)
        {
            var basic =await _db.Basic.Where(a => a.Did == depId).ToListAsync();
            return basic;
        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="name"></param>
        /// <param name="depId"></param>
        /// <param name="postId"></param>
        /// <param name="state"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<EntryPageIng> GetEntryList(string name, int depId, int postId, int state, int pageIndex, int pageSize)
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str =
                    " select e.Id, d.`Name` saffName,dep.`Name` DepName,p.`Name` PostName,d1.`Name` UpName,e.Edate,e.Edatess,e.Ezt from Entry  e join basic d on e.Xid=d.Id join Deptinfo dep on e.Did=dep.Id join positioninfo p on e.Tid=p.Id join basic d1 on e.Uid=d1.Id where 1=1";
                string strCount = " select count(1)  from Entry  e join basic d on e.Xid=d.Id join Deptinfo dep on e.Did=dep.Id join positioninfo p on e.Tid=p.Id join basic d1 on e.Uid=d1.Id where 1=1 ";
                if (!string.IsNullOrEmpty(name))
                {
                    str += $"  and  d.`Name` like '%{name}%' ";
                    strCount += $"  and  d.`Name` like '%{name}%' ";
                }

                if (depId != 0)
                {
                    str += $"  and  e.Did={depId}";
                    strCount += $"  and  e.Did={depId}";
                }
                if (postId != 0)
                {
                    str += $"  and  e.Tid={postId}";
                    strCount += $"  and  e.Tid={postId}";
                }
                if (state != -1)
                {
                    str += $"  and  e.Ezt={state}";
                    strCount += $"  and  e.Ezt={state}";
                }
                var page = (pageIndex - 1) * pageSize;
                str += $" limit {page},{pageSize}";
                var entry =(List<Entry>)await conn.QueryAsync<Entry>(str);
                var count =await conn.QueryFirstOrDefaultAsync<int>(strCount);
                var data = new EntryPageIng
                {
                    Item = entry,
                    Toacl = count,
                };
                return data;

            }
        }


        /// <summary>
        /// 删除入职数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DelEntryById(string id)
        {
            var ids = id.Split(',');
            foreach (var a in ids)
            {
                var obj =await _db.Entry.FindAsync(Convert.ToInt32(a));
                _db.Entry.Remove(obj);
            }

            int i = await _db.SaveChangesAsync();
            return i;
        }


        /// <summary>
        /// 通过编号获取入职数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public  async Task<Entry> GetEntryData(int id, int state)
        {
            if (state == 1)
            {
                using (IDbConnection conn = new MySqlConnection(SqlConn))
                {
                    string str =
                        " select e.Id, d.`Name` saffName,dep.`Name` DepName,p.`Name` PostName,d1.`Name` UpName,e.Edate,e.Edatess,e.Eremark from Entry  e join basic d on e.Xid=d.Id join Deptinfo dep on e.Did=dep.Id join positioninfo p on e.Tid=p.Id join basic d1 on e.Uid=d1.Id where e.Id=" + id;
                    var entry = await conn.QueryFirstOrDefaultAsync<Entry>(str);
                    return entry;
                }
            }
            else
            {
               var obj=await _db.Entry.FindAsync(id);
               return obj;
            }
            
        }

        /// <summary>
        /// 修改入职数据
        /// </summary>
        /// <param name="entry"></param>
        /// <returns></returns>
        public async Task<int> PutEntry(Entry entry)
        {
            _db.Entry(entry).State = EntityState.Modified;
            int i = await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 提交入职申请
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> SubmitEntry(int id)
        {
            var obj = _db.Entry.FindAsync(id).Result;
            obj.Ezt = 1;
            int i= await _db.SaveChangesAsync();
            return i;

        }

        #endregion

        #region 转正

        /// <summary>
        /// 新增转正记录
        /// </summary>
        /// <param name="become"></param>
        /// <returns></returns>
        public async Task<int> AddBecomeData(Become become)
        {
            become.Bdatess = DateTime.Now;
            become.Bzt = 0;//待提交
            await _db.Become.AddAsync(become);
            int i = await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 通过部门编号获取该部门的员工数据
        /// </summary>
        /// <param name="depId"></param>
        /// <returns></returns>
        public async Task<List<Become>> GetBecomeByDepId(int depId)
        {
            var basic = await _db.Become.Where(a => a.Did == depId).ToListAsync();
            return basic;
        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="name"></param>
        /// <param name="depId"></param>
        /// <param name="postId"></param>
        /// <param name="state"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<BecomePageIng> GetBecomeList(string name, int depId, int postId, int state, int pageIndex, int pageSize)
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str =
                    " select  be.Id,b.`Name` as SaffName,dep.`Name`  DepName,p.`Name` PostName ,d1.`Name` UpName ,be.Bdate,be.Bdatetime,be.Bopinion,be.Bzt,be.Bdatess from Become be join basic  b on be.xid=b.id  join Deptinfo dep on be.Did=dep.id join positioninfo p on be.Tid=p.Id join basic d1 on be.Uid=d1.Id where 1=1";
                string strCount = " select  count(1) from Become be join basic  b on be.xid=b.id  join Deptinfo dep on be.Did=dep.id join positioninfo p on be.Tid=p.Id join basic d1 on be.Uid=d1.Id where 1=1 ";
                if (!string.IsNullOrEmpty(name))
                {
                    str += $"  and b.`Name` like '%{name}%' ";
                    strCount += $"  and  b.`Name` like '%{name}%' ";
                }

                if (depId != 0)
                {
                    str += $"  and  be.Did={depId}";
                    strCount += $"  and  be.Did={depId}";
                }
                if (postId != 0)
                {
                    str += $"  and  be.Tid={postId}";
                    strCount += $"  and  be.Tid={postId}";
                }
                if (state != -1)
                {
                    str += $"  and  be.Bzt={state}";
                    strCount += $"  and  be.Bzt={state}";
                }
                var page = (pageIndex - 1) * pageSize;
                str += $" limit {page},{pageSize}";
                var become = (List<Become>)await conn.QueryAsync<Become>(str);
                var count = await conn.QueryFirstOrDefaultAsync<int>(strCount);
                var data = new BecomePageIng
                {
                    Item = become,
                    Toacl = count,
                };
                return data;

            }
        }


        /// <summary>
        /// 删除转正数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DelBecomeById(string id)
        {
            var ids = id.Split(',');
            foreach (var a in ids)
            {
                var obj = await _db.Become.FindAsync(Convert.ToInt32(a));
                _db.Become.Remove(obj);
            }

            int i = await _db.SaveChangesAsync();
            return i;
        }


        /// <summary>
        /// 通过编号获取转正数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public async Task<Become> GetBecomeData(int id, int state)
        {
            if (state == 1)
            {
                using (IDbConnection conn = new MySqlConnection(SqlConn))
                {
                    string str =
                        " select  be.Id,b.`Name` as SaffName,dep.`Name`  DepName,p.`Name` PostName ,d1.`Name` UpName ,be.Bdate,be.Bdatetime,be.Bopinion,be.Bzt,be.Bdatess,be.bsum from Become be join basic  b on be.xid=b.id  join Deptinfo dep on be.Did=dep.id join positioninfo p on be.Tid=p.Id join basic d1 on be.Uid=d1.Id where be.Id=" + id;
                    var become = await conn.QueryFirstOrDefaultAsync<Become>(str);
                    return become;
                }
            }
            else
            {
                var obj = await _db.Become.FindAsync(id);
                return obj;
            }

        }

        /// <summary>
        /// 修改转正数据
        /// </summary>
        /// <param name="become"></param>
        /// <returns></returns>
        public async Task<int> PutBecome(Become become)
        {
            _db.Entry(become).State = EntityState.Modified;
            int i = await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 提交转正申请
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> SubmitBecome(int id)
        {
            var obj = _db.Become.FindAsync(id).Result;
            obj.Bzt = 1;
            int i = await _db.SaveChangesAsync();
            return i;

        }

        #endregion

        #region 离职

        /// <summary>
        /// 新增离职记录
        /// </summary>
        /// <param name="dimission"></param>
        /// <returns></returns>
        public async Task<int> AddDimissionData(Dimission dimission)
        {
            dimission.Edatess = DateTime.Now;
            dimission.Ezt = 0;//待提交
            await _db.Dimission.AddAsync(dimission);
            int i = await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 通过部门编号获取该部门的员工数据
        /// </summary>
        /// <param name="depId"></param>
        /// <returns></returns>
        public async Task<List<Dimission>> GetDimissionByDepId(int depId)
        {
            var basic = await _db.Dimission.Where(a => a.Did == depId).ToListAsync();
            return basic;
        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="name"></param>
        /// <param name="depId"></param>
        /// <param name="postId"></param>
        /// <param name="state"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<DimissionPageIng> GetDimissionList(string name, int depId, int postId, int state, int pageIndex, int pageSize)
        {
            using (IDbConnection conn = new MySqlConnection(SqlConn))
            {
                string str =
                    " select  be.Id,b.`Name` as SaffName,dep.`Name`  DepName,p.`Name` PostName ,d1.`Name` UpName ,be.Edate,be.Dfinally,be.Dcause,be.Ezt,be.Edatess from Dimission be join basic  b on be.xid=b.id  join Deptinfo dep on be.Did=dep.id join positioninfo p on be.Tid=p.Id join basic d1 on be.Uid=d1.Id where 1=1";
                string strCount = " select  count(1) from Dimission be join basic  b on be.xid=b.id  join Deptinfo dep on be.Did=dep.id join positioninfo p on be.Tid=p.Id join basic d1 on be.Uid=d1.Id where 1=1";
                if (!string.IsNullOrEmpty(name))
                {
                    str += $"  and b.`Name` like '%{name}%' ";
                    strCount += $"  and  b.`Name` like '%{name}%' ";
                }

                if (depId != 0)
                {
                    str += $"  and  be.Did={depId}";
                    strCount += $"  and  be.Did={depId}";
                }
                if (postId != 0)
                {
                    str += $"  and  be.Tid={postId}";
                    strCount += $"  and  be.Tid={postId}";
                }
                if (state != -1)
                {
                    str += $"  and  be.Ezt={state}";
                    strCount += $"  and  be.Ezt={state}";
                }
                var page = (pageIndex - 1) * pageSize;
                str += $" limit {page},{pageSize}";
                var dimission = (List<Dimission>)await conn.QueryAsync<Dimission>(str);
                var count = await conn.QueryFirstOrDefaultAsync<int>(strCount);
                var data = new DimissionPageIng
                {
                    Item = dimission,
                    Toacl = count,
                };
                return data;

            }
        }


        /// <summary>
        /// 删除离职数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DelDimissionById(string id)
        {
            var ids = id.Split(',');
            foreach (var a in ids)
            {
                var obj = await _db.Dimission.FindAsync(Convert.ToInt32(a));
                _db.Dimission.Remove(obj);
            }

            int i = await _db.SaveChangesAsync();
            return i;
        }


        /// <summary>
        /// 通过编号获取离职数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public async Task<Dimission> GetDimissionData(int id, int state)
        {
            if (state == 1)
            {
                using (IDbConnection conn = new MySqlConnection(SqlConn))
                {
                    string str =
                        " select  be.Id,b.`Name` as SaffName,dep.`Name`  DepName,p.`Name` PostName ,d1.`Name` UpName ,be.Edate,be.Dfinally,be.Dcause,be.Ezt,be.Edatess from Dimission be join basic  b on be.xid=b.id  join Deptinfo dep on be.Did=dep.id join positioninfo p on be.Tid=p.Id join basic d1 on be.Uid=d1.Id where be.Id=" + id;
                    var dimission = await conn.QueryFirstOrDefaultAsync<Dimission>(str);
                    return dimission;
                }
            }
            else
            {
                var obj = await _db.Dimission.FindAsync(id);
                return obj;
            }

        }

        /// <summary>
        /// 修改离职数据
        /// </summary>
        /// <param name="Dimission"></param>
        /// <returns></returns>
        public async Task<int> PutDimission(Dimission Dimission)
        {
            _db.Entry(Dimission).State = EntityState.Modified;
            int i = await _db.SaveChangesAsync();
            return i;
        }

        /// <summary>
        /// 提交离职申请
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> SubmitDimission(int id)
        {
            var obj = _db.Dimission.FindAsync(id).Result;
            obj.Ezt = 1;
            int i = await _db.SaveChangesAsync();
            return i;

        }

        #endregion
    }
}